package com.xlsx;

import com.xlsx.model.sqlModel;
import com.xlsx.utils.ExcelUtils;
import com.xlsx.utils.WriteStringBufferToTxt;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;

public class createSqlPlus {

    public static void createSql(String tableName,String filePath,int state) throws IOException {
        //根据路径生成 FileInputStream字节流
        FileInputStream inputStream = new FileInputStream(new File(filePath));

        //通过ExcelUtils工具将Excel数据存入到list中，工具代码下面细讲,这一步读取Excel已经完成了，如果不想进行插入数据库操作，可以直接拿着list用啦。
        try {
            List<List<sqlModel>> list = ExcelUtils.getList(inputStream,filePath,state);
            StringBuffer stringBuffer = new StringBuffer();
            sqlModel sqlModel = null;
            for (int i=0;i<list.size();i++){
                // 拿到sheet页内容
                for (int j=0;j<list.get(i).size();j++){
                    sqlModel = list.get(i).get(j);
                    // 处理每一行的数据
                    if (j == 0){
                        if (sqlModel.getTableDetailed()!="" || sqlModel.getTableDetailed()!=null){
                            stringBuffer.append("-- " + sqlModel.getTableDetailed() + "\n");
                        }
                        // 删表
                        stringBuffer.append("-- drop table " + sqlModel.getTableName() + ";\n\n");
                        // 建表
                        stringBuffer.append("-- Create table").append("\n");
                        stringBuffer.append("CREATE TABLE "+ sqlModel.getTableName() ).append("\n");
                        stringBuffer.append("(").append("\n");
                    }

                    stringBuffer.append("        ").append(sqlModel.getField_Name() + " ").append(sqlModel.getDataType());
                    if (sqlModel.getIsNull().equals("N")){
                        stringBuffer.append(" NOT NULL");
                    }
                    if(j != list.get(i).size()-1){
                        stringBuffer.append(",\n");
                    }else {
                        stringBuffer.append(",\n");
                        stringBuffer.append("        ").append("CREATE_USER" + " ").append("VARCHAR2(32)").append(",\n");
                        stringBuffer.append("        ").append("CREATE_DATE" + " ").append("CHAR(19)").append(",\n");
                        stringBuffer.append("        ").append("UPDATE_USER" + " ").append("VARCHAR2(32)").append(",\n");
                        stringBuffer.append("        ").append("UPDATE_DATE" + " ").append("CHAR(19)").append(",\n");
                        stringBuffer.append("        ").append("DEF1" + " ").append("CHAR(19)").append(",\n"); // 扩展字段
                        stringBuffer.append("        ").append("DEF2" + " ").append("CHAR(100)").append(",\n");
                        stringBuffer.append("        ").append("DEF3" + " ").append("CHAR(200)");
                        if (list.get(i).get(0).getField_Name().equals("ID")||list.get(i).get(0).getField_Name().equals("id")){
                            stringBuffer.append(",\n").append("        ").append("PRIMARY KEY("+'"'+list.get(i).get(0).getField_Name()+'"'+")");
                        }
                        stringBuffer.append("\n");
                        stringBuffer.append(");").append("\n").append("\n");
                    }
                }
                for (int j=0;j<list.get(i).size();j++){

                    sqlModel = list.get(i).get(j);
                    if (j == 0){
                        // 增加备注  COMMENT ON COLUMN FTP_RATE_PROTECT.ID IS '主键';
                        stringBuffer.append("-- Add comments\n");
                        stringBuffer.append("COMMENT ON TABLE ").append(sqlModel.getTableName()).append(" IS '").append(sqlModel.getTableDetailed()+"';\n");
                    }

                    stringBuffer.append("COMMENT ON COLUMN ").append(sqlModel.getTableName()).append("."+sqlModel.getField_Name() + " IS '").append(sqlModel.getFieldDescription());

                    if (sqlModel.getDetailed()!= null && sqlModel.getDetailed() != ""){
                        stringBuffer.append("(" + sqlModel.getDetailed() + ")");
                    }
                    stringBuffer.append("';\n");
                    if(j == list.get(i).size()-1){
                        stringBuffer.append("\n").append("\n").append("\n");
                    }
                }

            }

            /**
             * 给txt传数据
             */
            WriteStringBufferToTxt toTxt = new WriteStringBufferToTxt();
            toTxt.writeToTxtFile(stringBuffer,tableName);
            System.out.println(stringBuffer.toString());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
